{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Window functions\n",
    "\n",
    "General Elections were held in the UK in 2015 and 2017. Every citizen votes in a constituency. The candidate who gains the most votes becomes MP for that constituency.\n",
    "\n",
    "All these results are recorded in a table ge\n",
    "\n",
    "yr\t| firstName\t| lastName\t| constituency\t| party\t| votes\n",
    "---:|-----------|-----------|---------------|-------|------:\n",
    "2015\t| Ian\t| Murray\t| S14000024\t| Labour\t| 19293\n",
    "2015\t| Neil\t| Hay\t| S14000024\t| Scottish National Party\t| 16656\n",
    "2015\t| Miles\t| Briggs\t| S14000024\t| Conservative | 8626\n",
    "2015\t| Phyl\t| Meyer\t| S14000024\t| Green\t| 2090\n",
    "2015\t| Pramod\t| Subbaraman\t| S14000024\t| Liberal Democrat\t| 1823\n",
    "2015\t| Paul\t| Marshall\t| S14000024\t| UK Independence Party\t | 601\n",
    "2015\t| Colin\t| Fox\t| S14000024\t| Scottish Socialist Party\t| 197\n",
    "2017\t| Ian\t| MURRAY\t| S14000024\t| Labour\t| 26269\n",
    "2017\t| Jim\t| EADIE\t| S14000024\t| SNP\t| 10755\n",
    "2017\t| Stephanie Jane Harley\t| SMITH\t| S14000024\t| Conservative\t| 9428\n",
    "2017\t| Alan Christopher\t| BEAL\t| S14000024\t| Liberal Democrats\t| 1388"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "-- \u001b[1mAttaching packages\u001b[22m --------------------------------------- tidyverse 1.3.0 --\n",
      "\n",
      "\u001b[32mv\u001b[39m \u001b[34mggplot2\u001b[39m 3.3.0     \u001b[32mv\u001b[39m \u001b[34mpurrr  \u001b[39m 0.3.4\n",
      "\u001b[32mv\u001b[39m \u001b[34mtibble \u001b[39m 3.0.1     \u001b[32mv\u001b[39m \u001b[34mdplyr  \u001b[39m 0.8.5\n",
      "\u001b[32mv\u001b[39m \u001b[34mtidyr  \u001b[39m 1.0.2     \u001b[32mv\u001b[39m \u001b[34mstringr\u001b[39m 1.4.0\n",
      "\u001b[32mv\u001b[39m \u001b[34mreadr  \u001b[39m 1.3.1     \u001b[32mv\u001b[39m \u001b[34mforcats\u001b[39m 0.5.0\n",
      "\n",
      "-- \u001b[1mConflicts\u001b[22m ------------------------------------------ tidyverse_conflicts() --\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mfilter()\u001b[39m masks \u001b[34mstats\u001b[39m::filter()\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mlag()\u001b[39m    masks \u001b[34mstats\u001b[39m::lag()\n",
      "\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Password? ·········\n"
     ]
    }
   ],
   "source": [
    "library(tidyverse)\n",
    "library(DBI)\n",
    "library(getPass)\n",
    "drv <- switch(Sys.info()['sysname'],\n",
    "             Windows=\"PostgreSQL Unicode(x64)\",\n",
    "             Darwin=\"/usr/local/lib/psqlodbcw.so\",\n",
    "             Linux=\"PostgreSQL\")\n",
    "con <- dbConnect(\n",
    "  odbc::odbc(),\n",
    "  driver = drv,\n",
    "  Server = \"localhost\",\n",
    "  Database = \"sqlzoo\",\n",
    "  UID = \"postgres\",\n",
    "  PWD = getPass(\"Password?\"),\n",
    "  Port = 5432\n",
    ")\n",
    "options(repr.matrix.max.rows=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Warming up\n",
    "\n",
    "Show the **lastName, party** and **votes** for the **constituency** 'S14000024' in 2017."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "ge <- dbReadTable(con, 'ge')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 4 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>lastname</th><th scope=col>party</th><th scope=col>votes</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>BEAL  </td><td>Liberal Democrats</td><td> 1388</td></tr>\n",
       "\t<tr><td>MURRAY</td><td>Labour           </td><td>26269</td></tr>\n",
       "\t<tr><td>EADIE </td><td>SNP              </td><td>10755</td></tr>\n",
       "\t<tr><td>SMITH </td><td>Conservative     </td><td> 9428</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 4 × 3\n",
       "\\begin{tabular}{lll}\n",
       " lastname & party & votes\\\\\n",
       " <chr> & <chr> & <int>\\\\\n",
       "\\hline\n",
       "\t BEAL   & Liberal Democrats &  1388\\\\\n",
       "\t MURRAY & Labour            & 26269\\\\\n",
       "\t EADIE  & SNP               & 10755\\\\\n",
       "\t SMITH  & Conservative      &  9428\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 4 × 3\n",
       "\n",
       "| lastname &lt;chr&gt; | party &lt;chr&gt; | votes &lt;int&gt; |\n",
       "|---|---|---|\n",
       "| BEAL   | Liberal Democrats |  1388 |\n",
       "| MURRAY | Labour            | 26269 |\n",
       "| EADIE  | SNP               | 10755 |\n",
       "| SMITH  | Conservative      |  9428 |\n",
       "\n"
      ],
      "text/plain": [
       "  lastname party             votes\n",
       "1 BEAL     Liberal Democrats  1388\n",
       "2 MURRAY   Labour            26269\n",
       "3 EADIE    SNP               10755\n",
       "4 SMITH    Conservative       9428"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ge %>% \n",
    "    filter(constituency=='S14000024' & yr==2017) %>%\n",
    "    select(lastname, party, votes)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Who won?\n",
    "\n",
    "You can use the RANK function to see the order of the candidates. If you RANK using (ORDER BY votes DESC) then the candidate with the most votes has rank 1.\n",
    "\n",
    "**Show the party and RANK for constituency S14000024 in 2017. List the output by party**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 4 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>party</th><th scope=col>rank</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Conservative     </td><td>3</td></tr>\n",
       "\t<tr><td>Labour           </td><td>1</td></tr>\n",
       "\t<tr><td>Liberal Democrats</td><td>4</td></tr>\n",
       "\t<tr><td>SNP              </td><td>2</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 4 × 2\n",
       "\\begin{tabular}{ll}\n",
       " party & rank\\\\\n",
       " <chr> & <int>\\\\\n",
       "\\hline\n",
       "\t Conservative      & 3\\\\\n",
       "\t Labour            & 1\\\\\n",
       "\t Liberal Democrats & 4\\\\\n",
       "\t SNP               & 2\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 4 × 2\n",
       "\n",
       "| party &lt;chr&gt; | rank &lt;int&gt; |\n",
       "|---|---|\n",
       "| Conservative      | 3 |\n",
       "| Labour            | 1 |\n",
       "| Liberal Democrats | 4 |\n",
       "| SNP               | 2 |\n",
       "\n"
      ],
      "text/plain": [
       "  party             rank\n",
       "1 Conservative      3   \n",
       "2 Labour            1   \n",
       "3 Liberal Democrats 4   \n",
       "4 SNP               2   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ge %>% \n",
    "    filter(constituency=='S14000024' & yr==2017) %>%\n",
    "    mutate(rank=row_number(desc(votes))) %>%\n",
    "    select(party, rank) %>% \n",
    "    arrange(party)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. PARTITION BY\n",
    "\n",
    "The 2015 election is a different PARTITION to the 2017 election. We only care about the order of votes for each year.\n",
    "\n",
    "**Use PARTITION to show the ranking of each party in S14000021 in each year. Include yr, party, votes and ranking (the party with the most votes is 1).**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 12 × 4</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>yr</th><th scope=col>party</th><th scope=col>votes</th><th scope=col>posn</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>2015</td><td>Conservative     </td><td>12465</td><td>3</td></tr>\n",
       "\t<tr><td>2017</td><td>Conservative     </td><td>21496</td><td>1</td></tr>\n",
       "\t<tr><td>2019</td><td>Conservative     </td><td>19451</td><td>2</td></tr>\n",
       "\t<tr><td>2015</td><td>Labour           </td><td>19295</td><td>2</td></tr>\n",
       "\t<tr><td>2017</td><td>Labour           </td><td>14346</td><td>2</td></tr>\n",
       "\t<tr><td>2019</td><td>Labour           </td><td> 6855</td><td>3</td></tr>\n",
       "\t<tr><td>2015</td><td>Liberal Democrats</td><td> 1069</td><td>4</td></tr>\n",
       "\t<tr><td>2017</td><td>Liberal Democrats</td><td> 1112</td><td>3</td></tr>\n",
       "\t<tr><td>2019</td><td>Liberal Democrats</td><td> 4174</td><td>4</td></tr>\n",
       "\t<tr><td>2015</td><td>SNP              </td><td>23013</td><td>1</td></tr>\n",
       "\t<tr><td>2019</td><td>SNP              </td><td>24877</td><td>1</td></tr>\n",
       "\t<tr><td>2015</td><td>UKIP             </td><td>  888</td><td>5</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 12 × 4\n",
       "\\begin{tabular}{llll}\n",
       " yr & party & votes & posn\\\\\n",
       " <int> & <chr> & <int> & <int>\\\\\n",
       "\\hline\n",
       "\t 2015 & Conservative      & 12465 & 3\\\\\n",
       "\t 2017 & Conservative      & 21496 & 1\\\\\n",
       "\t 2019 & Conservative      & 19451 & 2\\\\\n",
       "\t 2015 & Labour            & 19295 & 2\\\\\n",
       "\t 2017 & Labour            & 14346 & 2\\\\\n",
       "\t 2019 & Labour            &  6855 & 3\\\\\n",
       "\t 2015 & Liberal Democrats &  1069 & 4\\\\\n",
       "\t 2017 & Liberal Democrats &  1112 & 3\\\\\n",
       "\t 2019 & Liberal Democrats &  4174 & 4\\\\\n",
       "\t 2015 & SNP               & 23013 & 1\\\\\n",
       "\t 2019 & SNP               & 24877 & 1\\\\\n",
       "\t 2015 & UKIP              &   888 & 5\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 12 × 4\n",
       "\n",
       "| yr &lt;int&gt; | party &lt;chr&gt; | votes &lt;int&gt; | posn &lt;int&gt; |\n",
       "|---|---|---|---|\n",
       "| 2015 | Conservative      | 12465 | 3 |\n",
       "| 2017 | Conservative      | 21496 | 1 |\n",
       "| 2019 | Conservative      | 19451 | 2 |\n",
       "| 2015 | Labour            | 19295 | 2 |\n",
       "| 2017 | Labour            | 14346 | 2 |\n",
       "| 2019 | Labour            |  6855 | 3 |\n",
       "| 2015 | Liberal Democrats |  1069 | 4 |\n",
       "| 2017 | Liberal Democrats |  1112 | 3 |\n",
       "| 2019 | Liberal Democrats |  4174 | 4 |\n",
       "| 2015 | SNP               | 23013 | 1 |\n",
       "| 2019 | SNP               | 24877 | 1 |\n",
       "| 2015 | UKIP              |   888 | 5 |\n",
       "\n"
      ],
      "text/plain": [
       "   yr   party             votes posn\n",
       "1  2015 Conservative      12465 3   \n",
       "2  2017 Conservative      21496 1   \n",
       "3  2019 Conservative      19451 2   \n",
       "4  2015 Labour            19295 2   \n",
       "5  2017 Labour            14346 2   \n",
       "6  2019 Labour             6855 3   \n",
       "7  2015 Liberal Democrats  1069 4   \n",
       "8  2017 Liberal Democrats  1112 3   \n",
       "9  2019 Liberal Democrats  4174 4   \n",
       "10 2015 SNP               23013 1   \n",
       "11 2019 SNP               24877 1   \n",
       "12 2015 UKIP                888 5   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ge %>% \n",
    "    filter(constituency=='S14000021') %>%\n",
    "    group_by(yr) %>% \n",
    "    mutate(posn=row_number(desc(votes))) %>%\n",
    "    ungroup %>% \n",
    "    select(yr, party, votes, posn) %>%\n",
    "    arrange(party, yr)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Edinburgh Constituency\n",
    "\n",
    "Edinburgh constituencies are numbered S14000021 to S14000026.\n",
    "\n",
    "**Use PARTITION BY constituency to show the ranking of each party in Edinburgh in 2017. Order your results so the winners are shown first, then ordered by constituency.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 24 × 4</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>constituency</th><th scope=col>party</th><th scope=col>votes</th><th scope=col>posn</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>S14000021</td><td>Conservative     </td><td>21496</td><td>1</td></tr>\n",
       "\t<tr><td>S14000022</td><td>SNP              </td><td>18509</td><td>1</td></tr>\n",
       "\t<tr><td>S14000023</td><td>SNP              </td><td>19243</td><td>1</td></tr>\n",
       "\t<tr><td>S14000024</td><td>Labour           </td><td>26269</td><td>1</td></tr>\n",
       "\t<tr><td>S14000025</td><td>SNP              </td><td>17575</td><td>1</td></tr>\n",
       "\t<tr><td>S14000026</td><td>Liberal Democrats</td><td>18108</td><td>1</td></tr>\n",
       "\t<tr><td>S14000021</td><td>Labour           </td><td>14346</td><td>2</td></tr>\n",
       "\t<tr><td>S14000022</td><td>Labour           </td><td>15084</td><td>2</td></tr>\n",
       "\t<tr><td>S14000023</td><td>Labour           </td><td>17618</td><td>2</td></tr>\n",
       "\t<tr><td>S14000024</td><td>SNP              </td><td>10755</td><td>2</td></tr>\n",
       "\t<tr><td>...</td><td>...</td><td>...</td><td>...</td></tr>\n",
       "\t<tr><td>S14000023</td><td>Conservative     </td><td>15385</td><td>3</td></tr>\n",
       "\t<tr><td>S14000024</td><td>Conservative     </td><td> 9428</td><td>3</td></tr>\n",
       "\t<tr><td>S14000025</td><td>Labour           </td><td>13213</td><td>3</td></tr>\n",
       "\t<tr><td>S14000026</td><td>Conservative     </td><td>11559</td><td>3</td></tr>\n",
       "\t<tr><td>S14000023</td><td>Liberal Democrats</td><td> 2579</td><td>4</td></tr>\n",
       "\t<tr><td>S14000024</td><td>Liberal Democrats</td><td> 1388</td><td>4</td></tr>\n",
       "\t<tr><td>S14000025</td><td>Liberal Democrats</td><td> 2124</td><td>4</td></tr>\n",
       "\t<tr><td>S14000026</td><td>Labour           </td><td> 7876</td><td>4</td></tr>\n",
       "\t<tr><td>S14000023</td><td>Green            </td><td> 1727</td><td>5</td></tr>\n",
       "\t<tr><td>S14000026</td><td>SIR              </td><td>  132</td><td>5</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 24 × 4\n",
       "\\begin{tabular}{llll}\n",
       " constituency & party & votes & posn\\\\\n",
       " <chr> & <chr> & <int> & <int>\\\\\n",
       "\\hline\n",
       "\t S14000021 & Conservative      & 21496 & 1\\\\\n",
       "\t S14000022 & SNP               & 18509 & 1\\\\\n",
       "\t S14000023 & SNP               & 19243 & 1\\\\\n",
       "\t S14000024 & Labour            & 26269 & 1\\\\\n",
       "\t S14000025 & SNP               & 17575 & 1\\\\\n",
       "\t S14000026 & Liberal Democrats & 18108 & 1\\\\\n",
       "\t S14000021 & Labour            & 14346 & 2\\\\\n",
       "\t S14000022 & Labour            & 15084 & 2\\\\\n",
       "\t S14000023 & Labour            & 17618 & 2\\\\\n",
       "\t S14000024 & SNP               & 10755 & 2\\\\\n",
       "\t ... & ... & ... & ...\\\\\n",
       "\t S14000023 & Conservative      & 15385 & 3\\\\\n",
       "\t S14000024 & Conservative      &  9428 & 3\\\\\n",
       "\t S14000025 & Labour            & 13213 & 3\\\\\n",
       "\t S14000026 & Conservative      & 11559 & 3\\\\\n",
       "\t S14000023 & Liberal Democrats &  2579 & 4\\\\\n",
       "\t S14000024 & Liberal Democrats &  1388 & 4\\\\\n",
       "\t S14000025 & Liberal Democrats &  2124 & 4\\\\\n",
       "\t S14000026 & Labour            &  7876 & 4\\\\\n",
       "\t S14000023 & Green             &  1727 & 5\\\\\n",
       "\t S14000026 & SIR               &   132 & 5\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 24 × 4\n",
       "\n",
       "| constituency &lt;chr&gt; | party &lt;chr&gt; | votes &lt;int&gt; | posn &lt;int&gt; |\n",
       "|---|---|---|---|\n",
       "| S14000021 | Conservative      | 21496 | 1 |\n",
       "| S14000022 | SNP               | 18509 | 1 |\n",
       "| S14000023 | SNP               | 19243 | 1 |\n",
       "| S14000024 | Labour            | 26269 | 1 |\n",
       "| S14000025 | SNP               | 17575 | 1 |\n",
       "| S14000026 | Liberal Democrats | 18108 | 1 |\n",
       "| S14000021 | Labour            | 14346 | 2 |\n",
       "| S14000022 | Labour            | 15084 | 2 |\n",
       "| S14000023 | Labour            | 17618 | 2 |\n",
       "| S14000024 | SNP               | 10755 | 2 |\n",
       "| ... | ... | ... | ... |\n",
       "| S14000023 | Conservative      | 15385 | 3 |\n",
       "| S14000024 | Conservative      |  9428 | 3 |\n",
       "| S14000025 | Labour            | 13213 | 3 |\n",
       "| S14000026 | Conservative      | 11559 | 3 |\n",
       "| S14000023 | Liberal Democrats |  2579 | 4 |\n",
       "| S14000024 | Liberal Democrats |  1388 | 4 |\n",
       "| S14000025 | Liberal Democrats |  2124 | 4 |\n",
       "| S14000026 | Labour            |  7876 | 4 |\n",
       "| S14000023 | Green             |  1727 | 5 |\n",
       "| S14000026 | SIR               |   132 | 5 |\n",
       "\n"
      ],
      "text/plain": [
       "    constituency party             votes posn\n",
       "1   S14000021    Conservative      21496 1   \n",
       "2   S14000022    SNP               18509 1   \n",
       "3   S14000023    SNP               19243 1   \n",
       "4   S14000024    Labour            26269 1   \n",
       "5   S14000025    SNP               17575 1   \n",
       "6   S14000026    Liberal Democrats 18108 1   \n",
       "7   S14000021    Labour            14346 2   \n",
       "8   S14000022    Labour            15084 2   \n",
       "9   S14000023    Labour            17618 2   \n",
       "10  S14000024    SNP               10755 2   \n",
       "... ...          ...               ...   ... \n",
       "15  S14000023    Conservative      15385 3   \n",
       "16  S14000024    Conservative       9428 3   \n",
       "17  S14000025    Labour            13213 3   \n",
       "18  S14000026    Conservative      11559 3   \n",
       "19  S14000023    Liberal Democrats  2579 4   \n",
       "20  S14000024    Liberal Democrats  1388 4   \n",
       "21  S14000025    Liberal Democrats  2124 4   \n",
       "22  S14000026    Labour             7876 4   \n",
       "23  S14000023    Green              1727 5   \n",
       "24  S14000026    SIR                 132 5   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ge %>%\n",
    "    filter(constituency>='S14000021' & \n",
    "           constituency<='S14000026' &\n",
    "           yr==2017) %>%\n",
    "    group_by(constituency) %>% \n",
    "    mutate(posn=row_number(desc(votes))) %>%\n",
    "    ungroup %>% \n",
    "    select(constituency, party, votes, posn) %>%\n",
    "    arrange(posn, constituency)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Winners Only\n",
    "\n",
    "You can use [SELECT within SELECT](https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial) to pick out only the winners in Edinburgh.\n",
    "\n",
    "**Show the parties that won for each Edinburgh constituency in 2017.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 6 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>constituency</th><th scope=col>party</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>S14000021</td><td>Conservative     </td></tr>\n",
       "\t<tr><td>S14000022</td><td>SNP              </td></tr>\n",
       "\t<tr><td>S14000023</td><td>SNP              </td></tr>\n",
       "\t<tr><td>S14000024</td><td>Labour           </td></tr>\n",
       "\t<tr><td>S14000025</td><td>SNP              </td></tr>\n",
       "\t<tr><td>S14000026</td><td>Liberal Democrats</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 6 × 2\n",
       "\\begin{tabular}{ll}\n",
       " constituency & party\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t S14000021 & Conservative     \\\\\n",
       "\t S14000022 & SNP              \\\\\n",
       "\t S14000023 & SNP              \\\\\n",
       "\t S14000024 & Labour           \\\\\n",
       "\t S14000025 & SNP              \\\\\n",
       "\t S14000026 & Liberal Democrats\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 6 × 2\n",
       "\n",
       "| constituency &lt;chr&gt; | party &lt;chr&gt; |\n",
       "|---|---|\n",
       "| S14000021 | Conservative      |\n",
       "| S14000022 | SNP               |\n",
       "| S14000023 | SNP               |\n",
       "| S14000024 | Labour            |\n",
       "| S14000025 | SNP               |\n",
       "| S14000026 | Liberal Democrats |\n",
       "\n"
      ],
      "text/plain": [
       "  constituency party            \n",
       "1 S14000021    Conservative     \n",
       "2 S14000022    SNP              \n",
       "3 S14000023    SNP              \n",
       "4 S14000024    Labour           \n",
       "5 S14000025    SNP              \n",
       "6 S14000026    Liberal Democrats"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ge %>%\n",
    "    filter(constituency>='S14000021' & \n",
    "           constituency<='S14000026' &\n",
    "           yr==2017) %>%\n",
    "    group_by(constituency) %>%\n",
    "    mutate(posn=row_number(desc(votes))) %>%\n",
    "    ungroup %>% \n",
    "    filter(posn==1) %>%\n",
    "    select(constituency, party) %>%\n",
    "    arrange(constituency)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Scottish seats\n",
    "\n",
    "You can use **COUNT** and **GROUP BY** to see how each party did in Scotland. Scottish constituencies start with 'S'\n",
    "\n",
    "**Show how many seats for each party in Scotland in 2017.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 4 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>party</th><th scope=col>seats</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Conservative     </td><td>12</td></tr>\n",
       "\t<tr><td>Labour           </td><td> 9</td></tr>\n",
       "\t<tr><td>Liberal Democrats</td><td> 4</td></tr>\n",
       "\t<tr><td>SNP              </td><td>34</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 4 × 2\n",
       "\\begin{tabular}{ll}\n",
       " party & seats\\\\\n",
       " <chr> & <int>\\\\\n",
       "\\hline\n",
       "\t Conservative      & 12\\\\\n",
       "\t Labour            &  9\\\\\n",
       "\t Liberal Democrats &  4\\\\\n",
       "\t SNP               & 34\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 4 × 2\n",
       "\n",
       "| party &lt;chr&gt; | seats &lt;int&gt; |\n",
       "|---|---|\n",
       "| Conservative      | 12 |\n",
       "| Labour            |  9 |\n",
       "| Liberal Democrats |  4 |\n",
       "| SNP               | 34 |\n",
       "\n"
      ],
      "text/plain": [
       "  party             seats\n",
       "1 Conservative      12   \n",
       "2 Labour             9   \n",
       "3 Liberal Democrats  4   \n",
       "4 SNP               34   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ge %>%\n",
    "    filter(str_starts(constituency, 'S') & yr==2017) %>%\n",
    "    group_by(constituency) %>% \n",
    "    mutate(posn=row_number(desc(votes))) %>%\n",
    "    ungroup %>%\n",
    "    filter(posn==1) %>% \n",
    "    group_by(party) %>%\n",
    "    summarise(seats=n()) %>%\n",
    "    arrange(party)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbDisconnect(con)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "4.0.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
